package com.demo.dao.impl;

import java.math.BigInteger;
import java.util.Collections;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TemporalType;
import org.apache.commons.lang3.StringUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;
import com.demo.entity.User;

/**
 * 单做一个DAO类，以添加JPA接口没有的功能，比如动态sql查询
 * @author david
 *
 */
@Repository
public class UserDAOImpl {//如果要做组合查询(复杂查询)，才有必要写DAO的实现类，否则直接用DAO接口就好！
	
	@PersistenceContext
	private EntityManager entityManager;

	/**
	 * 动态sql的以拼接方式来处理
	 * @param pageable
	 * @param user
	 * @return
	 */
	public Page<User> search(Pageable pageable, User user) {//入参必须要给一个可分页对象，返回值必须是Page
		String fromSql = " FROM t_user u";
		StringBuilder dataSql = new StringBuilder("SELECT * ").append(fromSql);
		
		StringBuilder countSql = new StringBuilder("SELECT count(1)").append(fromSql);
		//+ "left join t_action a on v.video_id = a.video_id ");
		
		//拼接where条件
		StringBuilder whereSql = new StringBuilder(" WHERE 1 = 1"); //保证sql语法的正确性 from 表 where 1=1 and 字段1=值1 and 字段2=值2
		if (StringUtils.isNotEmpty(user.getSex())) {
			whereSql.append(" AND u.sex = :sex");
		}
		
		if (user.getHeight()!=null) {
			whereSql.append(" AND u.height = :height");
		}

		//后台输入日期截至范围进行查询
		if (user.getStartBirth() != null && user.getEndBirth() != null) {
			whereSql.append(" AND u.birth >= :startBirth AND u.birth <= :endBirth");
		}

		if (StringUtils.isNotEmpty(user.getUsername())) {
			whereSql.append(" AND u.username like concat('%',:username,'%')");  //%value%
		}													

		//组装sql语句
		dataSql.append(whereSql).append(" order by u.uid desc");
		countSql.append(whereSql);
		
		//创建本地sql查询实例
		System.out.print(dataSql.toString());
		System.out.print(countSql.toString());
		
		Query dataQuery = entityManager.createNativeQuery(dataSql.toString(), User.class);//调用实体管理器的创建本地查询方法执行sql
		Query countQuery = entityManager.createNativeQuery(countSql.toString());
		
		//设置参数的值
		if (StringUtils.isNotEmpty(user.getUsername())) {
			dataQuery.setParameter("username", user.getUsername());
			countQuery.setParameter("username", user.getUsername());
		}
		if (StringUtils.isNotEmpty(user.getSex())) {
			dataQuery.setParameter("sex", user.getSex());
			countQuery.setParameter("sex", user.getSex());
		}
		if (user.getHeight()!=null) {
			dataQuery.setParameter("height", user.getHeight());
			countQuery.setParameter("height", user.getHeight());
		}
		if (user.getStartBirth() != null && user.getEndBirth() != null) {
			dataQuery.setParameter("startBirth", user.getStartBirth(), TemporalType.TIMESTAMP);
			dataQuery.setParameter("endBirth", user.getEndBirth(), TemporalType.TIMESTAMP);
			countQuery.setParameter("startBirth", user.getStartBirth(), TemporalType.TIMESTAMP);
			countQuery.setParameter("endBirth", user.getEndBirth(), TemporalType.TIMESTAMP);
		}
		
		//log.info("sql="+dataQuery);
		//设置分页
		dataQuery.setFirstResult((int)pageable.getOffset()); //limit 10,20 起始位置(偏移位置)，在控制层实例化Pagenable，传入每页大小！
		dataQuery.setMaxResults(pageable.getPageSize());
		
		
		BigInteger count = (BigInteger) countQuery.getSingleResult();//执行统计条数的sql，拿到返回值是long
		Long total = count.longValue();//符合条件的总记录数
		
		
		//判断偏移量是否小于总记录数，如果没有越界访问返回List
		List<User> data = total > pageable.getOffset() ? dataQuery.getResultList() : Collections.<User> emptyList();
		return new PageImpl<>(data, pageable, total);//有了List数据后，封装成一个Page对象返回！控制层返回给页面的JSON数据会包含数据、还有分页信息
	}

}